﻿using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace Lg.Core.db
{
    public class SqlHelper
    {
        private string? cnnStr = string.Empty;

        #region 构造方法
        public SqlHelper()
        {
            this.cnnStr = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;
        }
        public SqlHelper(string str)
        {
            this.cnnStr = str;
        }
        #endregion

        #region [inset]
        /// <summary>
        /// 范型通用插入数据方法
        /// </summary>
        /// <typeparam name="T">范型类型</typeparam>
        /// <param name="t">范型实例</param>
        /// <param name="exclude">参数处理方式是否为排除，返之则是指定插入列</param>
        /// <param name="fields">要执行的字段集合</param>
        /// <returns>受影响的行数</returns>
        public int Insert<T>(T t, bool isBackIndex = false, bool exclude = false, params string[] fields)
        {
            Type type = typeof(T);
            PropertyInfo[] propertyInfos = null;
            if (exclude)
            {
                propertyInfos = type.GetProperties().Where(p => !fields.Contains(p.Name) && type.GetProperty(p.Name).GetValue(t) != null).ToArray();
            }
            else
            {
                propertyInfos = type.GetProperties().Where(p => fields.Contains(p.Name) && type.GetProperty(p.Name).GetValue(t) != null).ToArray();
            }
            string sql = $"Insert into [{type.Name}] ({string.Join(",", propertyInfos.Select(p => p.Name))}) values({string.Join(",", propertyInfos.Select(p => "@" + p.Name))})";
            List<SqlParameter> listParam = propertyInfos.Select(p => new SqlParameter("@" + p.Name, type.GetProperty(p.Name).GetValue(t))).ToList();
            if (isBackIndex)
            {
                sql += " Select SCOPE_IDENTITY()";
                return Convert.ToInt32(this.ExecuteScalar(sql, listParam));
            }
            return this.ExecuteNonQuery(sql, listParam);
        }

        public int Insert<T>(T t, bool exclude, params string[] fields)
        {
            Type type = typeof(T);
            PropertyInfo[] propertyInfos = null;
            if (exclude)
            {
                propertyInfos = type.GetProperties().Where(p => !fields.Contains(p.Name) && type.GetProperty(p.Name).GetValue(t) != null).ToArray();
            }
            else
            {
                propertyInfos = type.GetProperties().Where(p => fields.Contains(p.Name) && type.GetProperty(p.Name).GetValue(t) != null).ToArray();
            }
            string sql = $"Insert into [{type.Name}] ({string.Join(",", propertyInfos.Select(p => p.Name))}) values({string.Join(",", propertyInfos.Select(p => "@" + p.Name))})";
            List<SqlParameter> listParam = propertyInfos.Select(p => new SqlParameter("@" + p.Name, type.GetProperty(p.Name).GetValue(t))).ToList();
            return this.ExecuteNonQuery(sql, listParam);
        }
        #endregion

        #region [Delete]
        /// <summary>
        /// 根据自增字段删除记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="indexFieldName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public int Delete<T>(string indexFieldName, int value)
        {
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                Type type = typeof(T);
                string sql = $"delete from {type.Name} where {indexFieldName}={value}";
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cnn.Open();
                return cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region [eidt] 
        /// <summary>
        /// 范型通用编辑数据库记录
        /// </summary>
        /// <typeparam name="T">范型类弄</typeparam>
        /// <param name="t">范型实体</param>
        /// <param name="exclude">最后一个参数所包含的字段处理方式是否为排除</param>
        /// <param name="fields">要执行的字段数组</param>
        /// <returns></returns>
        public int Edit<T>(T t, bool exclude, params string[] fields)
        {
            Type type = typeof(T);
            PropertyInfo[] propertyInfos = null;
            if (exclude)
            {
                propertyInfos = type.GetProperties().Where(p => !fields.Contains(p.Name) || p.Name.ToLower() == "id").ToArray();
            }
            else
            {
                propertyInfos = type.GetProperties().Where(p => fields.Contains(p.Name) || p.Name.ToLower() == "id").ToArray();
            }
            string sql = $"update  [{type.Name}] set {string.Join(",", propertyInfos.Where(p => p.Name.ToLower() != "id").Select(p => $"[{p.Name}]=@{p.Name}"))} where ID=@Id";
            List<SqlParameter> listParam = propertyInfos.Select(p => new SqlParameter("@" + p.Name, type.GetProperty(p.Name).GetValue(t) == null ? DBNull.Value : type.GetProperty(p.Name).GetValue(t))).ToList();
            return this.ExecuteNonQuery(sql, listParam);
        }

        /// <summary>
        /// 范型编辑
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="listParam"></param>
        /// <returns></returns>
        public int Edit(string sql, List<SqlParameter> listParam)
        {
            return this.ExecuteNonQuery(sql, listParam);
        }
        #endregion

        #region [select]
        /// <summary>
        /// 范型单表通用数据库查询方法
        /// </summary>
        /// <typeparam name="T">范型类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="listParam">参数集合，无则给null</param>
        /// <returns></returns>
        public DataTable Select(string sql, List<SqlParameter> listParam)
        {
            if (listParam != null)
            {
                return this.Query(sql, listParam).Tables[0];
            }
            else
            {
                return this.Query(sql).Tables[0];
            }
        }
        public T Select<T>(string sql, List<SqlParameter> listParam) where T : new()
        {
            DataTable dt = null;
            if (listParam != null)
            {
                dt = this.Query(sql, listParam).Tables[0];
            }
            else
            {
                dt = this.Query(sql).Tables[0];
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                T model = new T();
                Type type = model.GetType();
                DataRow dr = dt.Rows[0];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo propertyInfo = type.GetProperty(dt.Columns[i].ColumnName);
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                    {
                        try
                        {
                            propertyInfo.SetValue(model, dr[i]);
                        }
                        catch
                        {
                            if (propertyInfo.PropertyType.Name == "String")
                            {
                                propertyInfo.SetValue(model, dr[i].ToString());
                            }
                        }
                    }

                }
                return model;
            }
            else
            {
                return default;
            }
        }
        /// <summary>
        /// 获取list实体集合
        /// </summary>
        /// <typeparam name="T">范型类型</typeparam>
        /// <param name="sql">sql字串</param>
        /// <param name="listParam">参数集合，无则为null</param>
        /// <returns></returns>
        public List<T> SelectList<T>(string sql, List<SqlParameter> listParam) where T : new()
        {
            DataTable dt = null;
            if (listParam != null)
            {
                dt = this.Query(sql, listParam).Tables[0];
            }
            else
            {
                dt = this.Query(sql).Tables[0];
            }
            if (dt != null && dt.Rows.Count > 0)
            {
                List<T> list = new List<T>();
                foreach (DataRow dr in dt.Rows)
                {
                    T model = new T();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        PropertyInfo propertyInfo = model.GetType().GetProperty(dt.Columns[i].ColumnName);
                        if (propertyInfo != null && dr[i] != DBNull.Value)
                        {
                            try
                            {
                                propertyInfo.SetValue(model, dr[i]);
                            }
                            catch
                            {

                            }
                        }
                    }
                    list.Add(model);
                }
                return list;
            }
            else
            {
                return null;
            }


        }
        /// <summary>
        /// 获取单值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="listParam">参数集合,可为null</param>
        /// <returns></returns>
        public object SelectValue(string sql, List<SqlParameter> listParam)
        {
            if (listParam != null)
            {
                return this.ExecuteScalar(sql, listParam);
            }
            else
            {
                return this.ExecuteScalar(sql);
            }
        }
        #endregion

        /// <summary>
        /// 真删操作
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="listParam">param</param>
        /// <returns></returns>
        public int Delete(string sql, List<SqlParameter> listParam)
        {
            if (listParam != null)
            {
                return this.ExecuteNonQuery(sql, listParam);
            }
            else
            {
                return this.ExecuteNonQuery(sql);
            }
        }

        #region[sql 原生方法]
        /// <summary>
        /// 增，删 ，改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        private int ExecuteNonQuery(string sql)
        {
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cnn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 有参数的 增，删 ，改
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="listParam">参数</param>
        /// <returns></returns>
        private int ExecuteNonQuery(string sql, List<SqlParameter> listParam)
        {
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                if (listParam != null)
                {
                    cmd.Parameters.AddRange(listParam.ToArray());
                }
                cnn.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 返回1行一列，无数据则为null,最大长度为2033个字符
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private object ExecuteScalar(string sql)
        {
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cnn.Open();
                return cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 返回1行一列，无数据则为null,最大长度为2033个字符
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="listParam"></param>
        /// <returns></returns>
        private object ExecuteScalar(string sql, List<SqlParameter> listParam)
        {
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cmd.Parameters.AddRange(listParam.ToArray());
                cnn.Open();
                return cmd.ExecuteScalar();
            }
        }
        #endregion

        #region [table to model or list]
        /// <summary>
        /// 将DataTable 转换为实体List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public List<T> GetList<T>(DataTable dt) where T : new()
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return null;
            }
            List<T> list = new List<T>();
            foreach (DataRow dr in dt.Rows)
            {
                T model = new T();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo propertyInfo = model.GetType().GetProperty(dt.Columns[i].ColumnName);
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                    {
                        try
                        {
                            propertyInfo.SetValue(model, dr[i]);
                        }
                        catch
                        {
                        }
                    }
                }
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// 将DataTable 转换为实体(单行数据)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public T GetModel<T>(DataTable dt) where T : new()
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                T model = new T();
                Type type = model.GetType();
                DataRow dr = dt.Rows[0];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo propertyInfo = type.GetProperty(dt.Columns[i].ColumnName);
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                        try
                        {
                            propertyInfo.SetValue(model, dr[i]);
                        }
                        catch
                        {
                        }
                }
                return model;
            }
            else
            {
                return default(T);
            }
        }
        #endregion

        #region [privite Query]
        private DataSet Query(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cnn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }

        private DataSet Query(string sql, List<SqlParameter> listParam)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cmd.Parameters.AddRange(listParam.ToArray());
                cnn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }
        #endregion     
    }
}
